# 1.导pymysql包
import pymysql

# 缩进外的finally中访问不到try中也是下面两个变量了，所有提取出来，变成全局变量，都能访问
conn = None
cursor = None

try:
    # 2.建立与数据库的连接
    conn = pymysql.connect(host='192.168.174.132', port=3306, user='root', password='iTest_2022_mysql', database='test_db',
                           charset='utf8')
    # 3.用连接，创建游标
    cursor = conn.cursor()
    # 4.用游标，execute执行sql语句
    cursor.execute('select * from t_book;')  # sql语句结束，要有分号;结束
    # 5.用游标,获取fetch*结果集res进行操作
    # 游标特性，提完游标会向下移动
    # 设置游标的位置，常用来归零0记录，用rownumber属性

    # 5.1 提取一条
    res1 = cursor.fetchone()
    print(res1)
    # (1, '射雕英雄传', datetime.date(1980, 5, 1), 12, 34, 0)
    print('-'*30)

    # 用rownumber归零0
    cursor.rownumber = 0
    # 5.2 提取全部
    res2 = cursor.fetchall()
    print(res2)
    # ((1, '射雕英雄传', datetime.date(1980, 5, 1), 12, 34, 0), (2, '天⻰⼋部', datetime.date(1986, 7, 24), 36, 40, 0), (3, '笑傲江湖', datetime.date(1995, 12, 24), 20, 80, 0), (4, '射雕英雄传2', datetime.date(1980, 5, 1), 12, 34, 0), (5, '天⻰⼋部2', datetime.date(1986, 7, 24), 36, 40, 0), (6, '笑傲江湖2', datetime.date(1995, 12, 24), 20, 80, 0))

    print('-'*30)
    # 5.3 提取3/4条
    # 指定游标位置，游标放在2记录，提取下一条从第3条记录开始
    cursor.rownumber = 2
    # 指定提取几条记录，2就是2条，就是第3条和第4条
    res3 = cursor.fetchmany(2)
    print(res3)
# ((3, '笑傲江湖', datetime.date(1995, 12, 24), 20, 80, 0), (4, '射雕英雄传2', datetime.date(1980, 5, 1), 12, 34, 0))
except Exception as err:
    print(err)  # 异常信息
    # (1146, "Table 'test_db.t_book1' doesn't exist")
finally:
    # 6.关闭游标cursor
    cursor.close()
    # 7.关闭连接conn
    conn.close()

# 加上异常处理try..except..finally，不会服务器崩溃1是0结束，且能打印异常信息 Process finished with exit code 1
# Process finished with exit code 0